package com.open.free.generator.service.impl;

import com.open.free.generator.entity.Columns;
import com.open.free.generator.entity.Source;
import com.open.free.generator.entity.Tables;
import com.open.free.generator.mapper.SourceMapper;
import com.open.free.generator.service.ISourceService;
import com.open.free.generator.utils.DataSourceFactory;
import com.open.free.generator.utils.FileIOUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.sql.*;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @author Faye.Wang
 * @version 1.0
 * @date 2020/12/28 22:52
 * @Description
 */
@Service
public class ISourceServiceImpl implements ISourceService {
    @Autowired
    private SourceMapper sourceMapper;

    private static Map<String, String> typeMap;

    static {
        typeMap = new HashMap<String, String>();
        typeMap.put("CLOB", "String");
        typeMap.put("TEXT", "String");
        typeMap.put("LONGTEXT", "String");
        typeMap.put("VARCHAR2", "String");
        typeMap.put("VARCHAR", "String");
        typeMap.put("INTEGER", "Integer");
        typeMap.put("INT", "Integer");
        typeMap.put("NUMBER", "Integer");
        typeMap.put("FLOAT", "float");
        typeMap.put("DECIMAL", "double");
        typeMap.put("TIMESTAMP", "Date");
        typeMap.put("CHAR", "String");
        typeMap.put("DATETIME", "Date");
        typeMap.put("DATE", "Date");
        typeMap.put("JSON", "String");
        typeMap.put("DATE_IMPORT", "import java.util.Date");
        typeMap.put("TIMESTAMP_IMPORT", "import java.util.Date");
        typeMap.put("DATETIME_IMPORT", "import java.util.Date");
    }



    @Override
    public void insertSource(Source source) {
        sourceMapper.insert(source);
    }

    @Override
    public List<Source> getDataSourceList(){
        return sourceMapper.selectList(null);
    }

    @Override
    public void delDataSource(Integer id) {
        sourceMapper.deleteById(id);
    }

    @Override
    public List<Tables> getTables(Source source) throws SQLException {
        Connection con = DataSourceFactory.getConnection(source);

        DatabaseMetaData dm=con.getMetaData();
        ResultSet rs=dm.getTables(con.getCatalog(), "%", "%", new String[]{"table"});
       List<Tables> tablesList = new ArrayList<>();
        Tables tables = null;
        while(rs.next()){
            tables = new Tables();
            String tableName = rs.getString("table_name");
            tables.setTableName(tableName);
            ResultSet columnsRs = dm.getColumns(null,"%", tableName,"%");
            List<Columns> columnsList = new ArrayList<>();
            Columns columns = null;
            while(columnsRs.next()){
                columns = new Columns();
                String columnName = columnsRs.getString("COLUMN_NAME");
                String columnType = columnsRs.getString("TYPE_NAME");
                String columnSize = columnsRs.getString("COLUMN_SIZE");

                columns.setName(columnName+" ("+columnType+" "+columnSize+")");
                columns.setSqlColumn(columnName);
                columnsList.add(columns);
            }
            tables.setColumns(columnsList);
            tablesList.add(tables);
        }
        con.close();
        return tablesList;
    }

    @Override
    public Source getSourceById(Integer id) {

        return sourceMapper.selectById(id);
    }

    @Override
    public  Map<String,Object> getSqlResultColumns(Source source,String sql) throws Exception {
        Connection con = DataSourceFactory.getConnection(source);
        PreparedStatement ps = con.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        ResultSetMetaData rsMetaData = rs.getMetaData();
        int columnCount = rsMetaData.getColumnCount();
        List<String> columnsTables = new ArrayList<>();

        for(int i=1;i<=columnCount;i++){
            columnsTables.add(rsMetaData.getColumnLabel(i));
        }

        List<Map<String, Object>> list = new ArrayList<>();
        while (rs.next()) {
            Map<String, Object> rowData = new HashMap<>();
            for (int i = 1; i <= columnCount; i++) {
                rowData.put(rsMetaData.getColumnLabel(i), rs.getObject(i));
            }
            list.add(rowData);
        }

        Map<String,Object> dataMap = new HashMap<>();
        dataMap.put("columnsTables",columnsTables);
        dataMap.put("list",list);
        con.close();

        return dataMap;
    }

    @Override
    public List<Columns> getTablesBean(Source source, String tableName) throws Exception {
        Connection con = DataSourceFactory.getConnection(source);

        DatabaseMetaData dm=con.getMetaData();
        ResultSet columnsRs = dm.getColumns(con.getCatalog(),"%", tableName,"%");
        List<Columns> columnsList = new ArrayList<>();
        Columns columns = null;
        while(columnsRs.next()){
            columns = new Columns();
            String columnName = columnsRs.getString("COLUMN_NAME");
            columns.setName(columnName);
            columnsList.add(columns);
        }
        return columnsList;
    }

    @Override
    public  List<Columns> getTableEntity(Source source, String tableName)  {
        List<Columns> columnsList = null;
        try {
            Connection con = DataSourceFactory.getConnection(source);
            DatabaseMetaData dbMetaData=con.getMetaData();

            // 获取当前表主键
            ResultSet primaryKeyResultSet = dbMetaData.getPrimaryKeys(con.getCatalog(),null,tableName);
            List<String> pkList = new ArrayList<>();
            while(primaryKeyResultSet.next()){
                String primaryKeyColumnName = primaryKeyResultSet.getString("COLUMN_NAME");
                pkList.add(primaryKeyColumnName);
            }
            if(pkList.size()>1){
                throw new Exception("MyBatis-plus 单表生成不支持符合主键，请更改");
            }
            // 获取当前表所有列
            ResultSet columnsRs = dbMetaData.getColumns(con.getCatalog(),"%", tableName,"%");

            columnsList = new ArrayList<>();
            Columns columns = null;
            while(columnsRs.next()){
                columns = new Columns();
                String columnName = columnsRs.getString("COLUMN_NAME");
                String temName = FileIOUtils.dealName(columnName,"_");
                columns.setSqlName(columnName);
                columns.setName(temName);
                columns.setPropertyName(toUpperCaseFirst(temName));
                if(columnName.equals(pkList.get(0))){
                  columns.setIsPrimaryKeys(true);
                  String isAutoincrement = columnsRs.getString("IS_AUTOINCREMENT");
                  columns.setIsAutoincrement(isAutoincrement);
                }
                String columnType = columnsRs.getString("TYPE_NAME");
                columns.setColumnComment(columnsRs.getString("REMARKS"));
                System.out.println(columnType);
                columns.setSqlColumn(getPojoType(columnType));
                columnsList.add(columns);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        return columnsList;
    }

    @Override
    public List<Columns> getTableVo(Source source, String tableName,List<String> voList,List<Columns> columnsList) throws Exception {
        Connection con = DataSourceFactory.getConnection(source);
        DatabaseMetaData dbMetaData=con.getMetaData();
        ResultSet columnsRs = dbMetaData.getColumns(con.getCatalog(),"%", tableName,"%");
        Columns columns = null;

        while(columnsRs.next()){
            columns = new Columns();
            String columnName = columnsRs.getString("COLUMN_NAME");

            List<String> list = voList.stream().filter(item->columnName.equals(item)).collect(Collectors.toList());
            if(!list.isEmpty()){
                String temName = FileIOUtils.dealName(columnName,"_");
                columns.setName(temName);
                columns.setPropertyName(toUpperCaseFirst(temName));
                String columnType = columnsRs.getString("TYPE_NAME");
                columns.setColumnComment(columnsRs.getString("REMARKS"));
                columns.setSqlColumn(getPojoType(columnType));
                columnsList.add(columns);
            }

        }

        return columnsList;
    }

    @Override
    public Columns getTableInfo(Source source, String tableName,String cname)  {
        Columns columns  = new Columns();
        try {
            Connection con = DataSourceFactory.getConnection(source);
            DatabaseMetaData dbMetaData=con.getMetaData();
            ResultSet columnsRs = dbMetaData.getColumns(con.getCatalog(),"%", tableName,cname);

            while(columnsRs.next()){

                String columnName = columnsRs.getString("COLUMN_NAME");
                String temName = FileIOUtils.dealName(columnName,"_");
                columns.setName(temName);
                columns.setPropertyName(toUpperCaseFirst(temName));
                String columnType = columnsRs.getString("TYPE_NAME");
                columns.setColumnComment(columnsRs.getString("REMARKS"));
                columns.setSqlColumn(getPojoType(columnType));

            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }


        return columns;
    }

    private  String getPojoType(String dataType) {
        StringTokenizer st = new StringTokenizer(dataType);
        return typeMap.get(st.nextToken());
    }
    /**
     * 将列明首字母大写 ColumName
     *
     * @param columnName
     * @return
     */
    private String toUpperCaseFirst(String columnName) {
        return columnName.substring(0, 1).toUpperCase() + columnName.substring(1, columnName.length());
    }



}
